﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;

namespace DatabaseAdminTools.Connections
{
    class SqlTable
    {
        public String Database { set; get; }
        public String Name { set; get; }
        private Connection Conn = null;

        public String[] Columns { get; private set; }
        public String[] PrimaryKeykFields { get; private set; }

        public SqlTable(Connection conn, String tableName, String dbName)
        {
            Database = dbName;
            Name = tableName;
            this.Conn = conn;
        }

        public void setValues()
        {
            // set columns
            Columns = getColumns();
            // set pk columns
            string sql = "SELECT distinct column_name FROM INFORMATION_SCHEMA.columns "
                        + "where INFORMATION_SCHEMA.columns.Table_Schema = '" + Database + "' "
                        + "and INFORMATION_SCHEMA.columns.Table_Name = '" + Name + "' "
                        + "and INFORMATION_SCHEMA.columns.column_key = 'PRI';";
            DataTable data = Conn.getTable(sql);
            if (data != null)
            {
                int rows = data.Rows.Count;
                PrimaryKeykFields = new string[rows];
                for (int i = 0; i < rows; i++)
                {
                    PrimaryKeykFields[i] = data.Rows[i][0].ToString();
                }
            }
        }

        public string[] getColumns()
        {
            string[] result = null;
            String sql = "SELECT distinct column_name FROM INFORMATION_SCHEMA.columns "
                            + "where INFORMATION_SCHEMA.columns.Table_Schema = '" + Database + "' "
                            + "and INFORMATION_SCHEMA.columns.Table_Name = '" + Name + "';";
            var data = Conn.getTable(sql);
            if (data != null)
            {
                int rows = data.Rows.Count;
                result = new string[rows];
                for (int i = 0; i < rows; i++)
                {
                    result[i] = data.Rows[i][0].ToString();
                }
            }
            return result;
        }

        public override string ToString()
        {
            return Name + "@" + Database + " [" + Conn.Name + "]";
        }

        public DataTable Data
        {
            get
            {
                return Conn.getTable("SELECT * FROM " + Database + "." + Name + ";");
            }
        }

        public DataTable ColumnProperties
        {
            get
            {
                String sql = "SELECT * FROM INFORMATION_SCHEMA.columns "
                        + "where INFORMATION_SCHEMA.columns.Table_Schema = '" + Database + "' "
                        + "and INFORMATION_SCHEMA.columns.Table_Name = '" + Name + "';";
                return Conn.getTable(sql);
            }
        }

        public DataTable Info
        {
            get
            {
                String sql = "SELECT Table_Schema as 'Database', Table_Name as 'Table Name', Table_Type as 'Type', engine as 'Engine', Version, row_format as 'Row Format', table_rows as 'Rows',"
                        + "data_length as 'Data Length', avg_row_length as 'Average Row Length', max_data_length as 'Max Data Length', index_length as 'Index Length', Data_Free as 'Data Free',"
                        + "Auto_Increment as 'Auto Increment Value', create_time as 'Create Time', update_time as 'Update Time', check_time as 'Check Time', table_collation as 'Table Collation',"
                        + "checksum as 'Checksum', create_options as 'Create Options', table_comment as 'Table Comment'"
                        + " FROM INFORMATION_SCHEMA.tables "
                        + "where INFORMATION_SCHEMA.tables.Table_Schema = '" + Database + "' "
                        + "and INFORMATION_SCHEMA.tables.Table_Name = '" + Name + "';";
                var data = Conn.getTable(sql);
                var data1 = new DataTable();
                data1.Columns.Add("Name");
                data1.Columns.Add("Value");
                int cols = data.Columns.Count;
                for (int i = 0; i < cols; i++)
                {
                    data1.Rows.Add(data.Columns[i], data.Rows[0][i]);
                }
                return data1;
            }
        }

        private DataRow getPrimarykeyValues(int row)
        {
            string sql = "SELECT ";
            int i;
            for (i = 0; i < this.PrimaryKeykFields.Length - 1; i++)
            {
                sql += this.PrimaryKeykFields[i] + ", ";
            }
            sql += this.PrimaryKeykFields[i] + " FROM " + Database + "." + Name + " limit " + row + ",1 ;";
            var data = Conn.getTable(sql);
            return data.Rows[0];
        }

        public DataRow getRow(int row)
        {
            var data = Conn.getTable("SELECT * FROM " + Database + "." + Name + " limit " + row + ",1;");
            return data.Rows[0];
        }

        public void update(int row, DataRow newValue)
        {
            DataRow pkValues = getPrimarykeyValues(row);
            string sql = "UPDATE " + Name + " SET ";
            int i;
            for (i = 0; i < this.Columns.Length - 1; i++)
            {
                sql += this.Columns[i] + " = " + (newValue[i].Equals("") ? "null" : "'" + newValue[i] + "'") + ", ";
            }
            sql += this.Columns[i] + " = " + (newValue[i].Equals("") ? "null" : "'" + newValue[i] + "'") + " WHERE ";
            i = 0;
            for (i = 0; i < this.PrimaryKeykFields.Length - 1; i++)
            {
                sql += this.PrimaryKeykFields[i] + " = '" + pkValues[i] + "' AND ";
            }
            sql += this.PrimaryKeykFields[i] + " = '" + pkValues[i] + "'";
            sql += " LIMIT 1;";
            Conn.executeQuery(sql);
        }

        public int deleteRecord(System.Windows.Forms.DataGridViewRow values)
        {
            string sql = "DELETE FROM " + Database + "." + Name + " WHERE ";
            int i;
            String val;
            for (i = 0; i < this.Columns.Length - 1; i++)
            {
                val = values.Cells[i].Value.ToString();
                sql += "(" + this.Columns[i] + (val.Equals("True") ? "=1" : (val.Equals("False") ? "=0" : (val.Equals("") ? " is NULL or " + this.Columns[i] + " = ''" : "='" + val + "'"))) + ") and ";
            }
            val = values.Cells[i].Value.ToString();
            sql += "(" + this.Columns[i] + (val.Equals("True") ? "=1" : (val.Equals("False") ? "=0" : (val.Equals("") ? " is NULL or " + this.Columns[i] + " = '');" : "='" + val + "') LIMIT 1")));
            return Conn.executeQuery(sql);
        }
    }
}
